﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using Ubatic.Business.Helpers;
using Ubatic.Common.Dto;

namespace Ubatic.Business.Managers
{
    public class RoleManager
    {
        #region [Roles]

        public long CreateRole(Role roleDto)
        {
            long result = 0;
            object resultId = null;
            string commandText = @"INSERT INTO [Roles]
                                               ([Name]
                                               ,[Description])
                                         VALUES
                                               (@Name
                                               ,@Description); SELECT @@IDENTITY;";

            using (DbConnection connection = DbHelper.CreateDbConnection())
            {
                connection.Open();

                DbCommand command = connection.CreateCommand();
                DbTransaction transaction;

                // Start a local transaction.
                transaction = connection.BeginTransaction();

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction
                command.Connection = connection;
                command.Transaction = transaction;

                command.CommandText = commandText;

                DbHelper.AddCommandParameter(command, "Name", roleDto.Name);
                DbHelper.AddCommandParameter(command, "Description", roleDto.Description);

                try
                {
                    resultId = command.ExecuteScalar();
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }

            if (resultId != null)
            {
                result = long.Parse(resultId.ToString());
            }

            return result;
        }

        public bool UpdateRole(Role roleDto)
        {
            int result = 0;
            string queryString = @"UPDATE [Roles]
                                       SET [Name]=@Name,
                                           [Description]=@Description
                                     WHERE Id=@Id";

            using (DbConnection connection = DbHelper.CreateDbConnection())
            {
                connection.Open();

                DbCommand command = connection.CreateCommand();
                DbTransaction transaction;

                // Start a local transaction.
                transaction = connection.BeginTransaction();

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction
                command.Connection = connection;
                command.Transaction = transaction;
                command.CommandText = queryString;

                DbHelper.AddCommandParameter(command, "Id", roleDto.Id);
                DbHelper.AddCommandParameter(command, "Name", roleDto.Name);
                DbHelper.AddCommandParameter(command, "Descritpion", roleDto.Description);

                try
                {
                    result = command.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    if (connection != null)
                        connection.Close();
                }

                return (result > 0);
            }
        }

        public bool DeleteRole(long id)
        {
            int result = 0;
            string queryString = "DELETE FROM Roles WHERE Id=@id";
            using (DbConnection connection = DbHelper.CreateDbConnection())
            {
                connection.Open();
                DbCommand command = connection.CreateCommand();

                command.CommandText = queryString;
                DbHelper.AddCommandParameter(command, "id", id);

                try
                {
                    result = command.ExecuteNonQuery();
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connection != null)
                        connection.Close();
                }
            }
            return (result > 0);
        }

        public Role GetRole(long id)
        {
            Role role = null;
            string queryString = "SELECT * FROM [Roles] WHERE Id=@id;";
            using (DbConnection connection = DbHelper.CreateDbConnection())
            {
                DbCommand command = connection.CreateCommand();
                command.CommandText = queryString;

                DbHelper.AddCommandParameter(command, "id", id);
                connection.Open();
                DbDataReader reader = command.ExecuteReader();
                try
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        role = RoleManager.ToRoleDto(reader);
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }
            return role;
        }

        public List<Role> GetRoles()
        {
            List<Role> roles = new List<Role>();
            string queryString = "SELECT * FROM Roles;";
            using (DbConnection connection = DbHelper.CreateDbConnection())
            {
                DbCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                DbDataReader reader = command.ExecuteReader();
                try
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            roles.Add(RoleManager.ToRoleDto(reader));
                        }
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }
            return roles;
        }

        internal List<Role> GetRolesByUser(long userId)
        {
            List<Role> roles = new List<Role>();
            string queryString = @"SELECT *
                                     FROM Roles r
                                    WHERE EXISTS (SELECT 1
                                                    FROM UsersRoles
                                                   WHERE RoleId = r.Id
                                                     AND UserId = @UserId);";
            using (DbConnection connection = DbHelper.CreateDbConnection())
            {
                DbCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                DbHelper.AddCommandParameter(command, "UserId", userId);

                connection.Open();
                DbDataReader reader = command.ExecuteReader();
                try
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            roles.Add(RoleManager.ToRoleDto(reader));
                        }
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }
            return roles;
        }

        #endregion

        private static Role ToRoleDto(DbDataReader reader)
        {
            return new Role()
            {
                Id = long.Parse(reader["Id"].ToString()),
                Name = reader["Name"].ToString(),
                Description = reader["Description"].ToString(),
            };
        }
    }
}
